package com.backup.common.utils;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.IdUtil;
import com.alibaba.fastjson2.JSON;
import com.backup.common.utils.excel.FieldVO;
import com.backup.common.vo.BackupJobVO;
import com.backup.common.vo.BackupRespVO;
import com.backup.common.vo.NodeProperties;
import com.backup.entity.BackupLog;
import com.backup.mapper.BackupLogMapper;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import lombok.val;
import org.apache.commons.collections4.map.LinkedMap;
import org.apache.commons.lang3.StringUtils;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.io.RandomAccessFile;
import java.math.BigInteger;
import java.nio.charset.StandardCharsets;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据源工具类
 *
 * @author backup
 */
@Slf4j
@Component
public class SqlBackUpUtil {

    @Resource
    private BackupLogMapper backupLogMapper;
    @Autowired
    private BackupFileUtil backupFileUtil;

    /**
     * 项目路径
     */
    public static final String PROJECT_PATH = System.getProperty("user.dir");

    /**
     * 当前系统类型
     */
    public static final String OS_NAME = System.getProperty("os.name");

    /**
     * 编码格式
     */
    public static final String CHAR_SET = OS_NAME.startsWith("Win") ? "GBK" : "UTF-8";


    /**
     * 数据库备份文本前缀
     */
    public static String ONESQL_PREFIX = "";

    private static String SUFFIX = "sql";
    private static String SPLIT = "`";
    private static String BR = "\r\n";
    private static String SLASH = "/";
    private static String SPACE = " ";
    private static String BRANCH = ";";
    private static String INSERT_INTO = " INSERT INTO ";
    private static String VALUES = "VALUES";
    private static String LEFTBRACE = "(";
    private static String RIGHTBRACE = ")";
    private static String QUOTES = "'";
    private static String COMMA = ",";
    private static String DISABLEFOREIGN = "SET FOREIGN_KEY_CHECKS = 0;\r\n";
    private static String ABLEFOREIGN = "SET FOREIGN_KEY_CHECKS = 1;\r\n";
    private static String CREATE_TABLE = "CREATE TABLE ";
    private static String CREATE_TABLE_ID = " `id` int(11) unsigned NOT NULL AUTO_INCREMENT ";
    private static String CREATE_TABLE_VARCHAR = " varchar(200) DEFAULT NULL COMMENT ";
    private static String CREATE_TABLE_PRIMARY = " PRIMARY KEY (`id`) ";
    private static String CREATE_TABLE_CHARSET = " ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ";

    private static final List<String> FILTER_DATABASE = Arrays.asList("information_schema", "mysql", "performance_schema", "sys");


    /**
     * 异步执行备份任务，更新备份记录
     *
     * @param jobVO     备份参数
     * @param backupLog 备份记录
     */
    @Async
    public void saveBackUp(BackupJobVO jobVO, BackupLog backupLog) {
        // 执行备份
        BackupRespVO respVO = doBackup(jobVO);
        backupLog.setStatus(respVO.isSuccess() ? (jobVO.getIsUpload() ? 1 : 2) : -1);
        backupLog.setMsg(respVO.getMsg());
        // 备份成功
        if (respVO.isSuccess()) {
            // 文件相对路径
            backupLog.setFilePath(respVO.getFile().getPath().replace(MysqlTool.PROJECT_PATH + File.separator, ""));
            backupLog.setFileSize(respVO.getFile().length());


        }
        // 备份失败
        if (!respVO.isSuccess()) {
            if (null != respVO.getFile()) {
                respVO.getFile().delete();
            }
        }
        // 计算耗时
        backupLog.setEndTime(new Date());
        backupLog.setSpendTime(backupLog.getEndTime().getTime() - backupLog.getStartTime().getTime());
        backupLogMapper.updateById(backupLog);
        // 备份文件上传至文件服务器
        if (!jobVO.getIsUpload()) {
            return;
        }
        backupFileUtil.uploadBackupFile(respVO, backupLog);
    }

    /**
     * 执行备份命令
     *
     * @param jobVO 备份参数
     */
    public BackupRespVO doBackup(BackupJobVO jobVO) {
        // 返回对象
        BackupRespVO respVO = new BackupRespVO();
        try {
            // 当前年月日
            String ymd = MysqlTool.getDate();
            // 文件目录
            String path = PROJECT_PATH + File.separator + "static" + (StringUtils.isNotBlank(jobVO.getDatabase()) ? (File.separator + jobVO.getDatabase()) : null) + File.separator + ymd + File.separator;
            // 文件名
            String fileName = IdUtil.fastSimpleUUID() + ".sql" + (jobVO.getIsCompress() ? ".zip" : "");
            // 创建文件
            File file = new File(path, fileName);
            // 路径不存在，则新建
            if (!file.getParentFile().exists()) {
                boolean flag = file.getParentFile().mkdirs();
                if (!flag) {
                    log.error("文件夹创建失败");
                    respVO.setMsg("文件夹创建失败");
                    return respVO;
                }
            }
            respVO.setFile(file);

            /**
             * 备份形式
             * 0-只备份表结构
             * 1-只备份表数据
             * null-备份表结构+表数据
             */
            List<String> sqlList = new ArrayList<>();
            log.info(jobVO.getDataType() + "");
            if(null == jobVO.getDataType()){
                sqlList = createTableSql(jobVO);
                sqlList.addAll(tablesInsertSql(jobVO));
            }else if(0 == jobVO.getDataType()){
                sqlList = createTableSql(jobVO);
            }else if(1 == jobVO.getDataType()){
                sqlList = createTableSql(jobVO);
            }

            if(sqlList.size() < 1){
                log.error("没有需要备份的数据");
            }


            //数据写入文件
            for (String sql : sqlList) {
                this.writeFile(respVO.getFile().getPath(), sql);
            }

            //打压缩包，加密
            ZipUtil.zip(path, "123456");

        } catch (Exception e) {
            e.printStackTrace();
            respVO.setMsg("【" + jobVO.getDatabase() + "】备份失败，原因：" + e.getMessage());
        }
        return respVO;
    }

    public static void writeFile(String path, String data){
        //文件地址
        try {
            // 打开一个随机访问文件流，按读写方式
            RandomAccessFile randomFile = new RandomAccessFile(path, "rw");
            // 文件长度，字节数
            long fileLength = randomFile.length();
            // 将写文件指针移到文件尾。
            randomFile.seek(fileLength);
            randomFile.write(data.getBytes(StandardCharsets.UTF_8));
            randomFile.writeBytes(BR);
            randomFile.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获取数据库所有库的信息
     */
    public static List<String> listDataBase(DataSource dataSource) {
        try {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            List<String> databases = template.query("SHOW DATABASES", new RowMapper<String>() {
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString(1);
                }
            }, null);
            return filterDatabase(databases);
        } catch (Exception e) {
            log.error("获取数据库信息失败：{}", e.getMessage());
        }
        return null;
    }

    /**
     * 获取数据库创建信息
     */
    public static List<String> createDatabaseSql(DataSource dataSource, String dbName) {

        try {
            JdbcTemplate template = new JdbcTemplate(dataSource);
            return template.query("SHOW CREATE DATABASE IF NOT EXISTS " + dbName, new RowMapper<String>() {
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString(1);
                }
            }, null);
        } catch (Exception e) {
            log.error("获取数据库信息失败：{}", e.getMessage());
        }
        return null;
    }

    /**
     * 获取数据库创建表信息
     */
    public static List<String> createTableSql(BackupJobVO backupJobVO) {
        try {
            log.info("createTableSql");
            List<String> resList = new ArrayList<>();
            JdbcTemplate template = new JdbcTemplate(getConnByBackupJobVO(backupJobVO));

            List<String> tableList = listTables(template);
            for (String table : tableList) {
                List<String> tableSql = template.query("SHOW CREATE TABLE " + table, new RowMapper<String>() {
                    public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                        return rs.getString(2);
                    }
                }, null);

                for (String s : tableSql) {
                    resList.add(s + BRANCH);
                }
            }
            return resList;

        } catch (Exception e) {
            e.printStackTrace();
            log.error("获取创建表信息失败：{}", e.getMessage());
        }
        return null;
    }

    /**
     * 备份所有数据/备份多表数据
     */
    public static List<String> tablesInsertSql(BackupJobVO jobVO) {
        try {

            List<String> sqlList = new ArrayList<>();
            JdbcTemplate template = new JdbcTemplate(getConnByBackupJobVO(jobVO));
            String[] tables = jobVO.getTables();
            if (null == tables || tables.length < 1){
                List<String> tableList = listTables(template);
                for (String table : tableList) {
                    sqlList.addAll(tableInsertSql(template, table));
                }
            }else{
                for (String table : tables) {
                    sqlList.addAll(tableInsertSql(template, table));
                }
            }

            return sqlList;

        } catch (Exception e) {
            e.printStackTrace();
            log.error("备份所有数据失败：{}", e.getMessage());
        }
        return null;
    }

    /**
     * 单表数据备份
     */
    public static List<String> tableInsertSql(JdbcTemplate template,  String tableName) {
        List<String> resList = new ArrayList<>();
        try {
            List<String> tableField = tableField(template, tableName);

            String collect = String.join(",", tableField);
            List<Map<String, Object>> maps = template.queryForList("select " + collect + " from " + tableName);

            return insertSql(tableName, maps);

        } catch (Exception e) {
            e.printStackTrace();
            log.error("单表 {" + tableName + "} 数据备份失败：{}", e.getMessage());
        }
        return resList;
    }

    /**
     * 拼接insert数据
     */
    public static List<String> insertSql(Object tableName, List<Map<String, Object>> resList) {
        List<String> sqlList = new ArrayList<>();

        if (resList.size() < 1) {
            //拼接insert语句
            return sqlList;
        }
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(ONESQL_PREFIX).append(INSERT_INTO).append(SPLIT).append(tableName).append(SPLIT).append(SPACE).append(VALUES).append(LEFTBRACE);

        //循环每一条数据
        for (Map<String, Object> map : resList) {
            if (null == map) {
                break;
            }

            //每一条都是单独的insert语句
            StringBuilder builder = new StringBuilder();
            builder.append(stringBuilder);

            //循环每一个map, 拼接values里的内容
            for (String key : map.keySet()) {
                Object data = map.get(key);

                if (null == data || "" == data) {
                    builder.append(QUOTES).append(QUOTES);
                } else {

                    //日期大写T的问题
//                    log.error(data.getClass().toString());

                    if (data instanceof Date || data instanceof LocalDateTime || data instanceof DateTime) {
                        builder.append(QUOTES).append(data).append(QUOTES);
                    } else if (data instanceof String) {

                        builder.append(QUOTES).append(replaceKeyString((String) data)).append(QUOTES);
                    } else if (data instanceof Boolean) {
                        if ((Boolean) data) {
                            builder.append(1);
                        } else {
                            builder.append(0);
                        }
                    } else if (data.getClass().toString().equalsIgnoreCase("class [B")) {
                        //处理blob类型数据
                        builder.append("0x")
                                .append(new BigInteger(1, byte[].class.cast(data)).toString(16).toUpperCase());
                    } else {
                        builder.append(data);
                    }
                }
                //加一个个逗号
                builder.append(COMMA);
            }
            builder.deleteCharAt(builder.length()-1);
            builder.append(RIGHTBRACE).append(BRANCH);
            sqlList.add(builder.toString());
        }
//        buffer = buffer.deleteCharAt(buffer.lastIndexOf(COMMA));
//        return buffer.toString();
        return sqlList;
    }

    // 将""和'转义
    public static String replaceKeyString(String str) {
        if (containsKeyString(str)) {
            return str.replace("'", "\\'").replace("\"", "\\\"").replace("\r",
                    "\\r").replace("\n", "\\n").replace("\t", "\\t").replace(
                    "\b", "\\b").replace("\f", "\\f");
        } else {
            return str;
        }
    }

    public static boolean containsKeyString(String str) {
        if (StringUtils.isBlank(str)) {
            return false;
        }
        if (str.contains("'") || str.contains("\"") || str.contains("\r")
                || str.contains("\n") || str.contains("\t")
                || str.contains("\b") || str.contains("\f")) {
            return true;
        }
        return false;
    }

    /**
     * 查表字段
     */
    public static List<String> tableField(JdbcTemplate template,  String tableName) {
        try {
            return template.query("desc " + tableName, new RowMapper<String>() {
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString(1);
                }
            }, null);

        } catch (Exception e) {
            e.printStackTrace();
            log.error("获取表字段信息失败：{}", e.getMessage());
        }
        return null;
    }

    /**
     * 获取数据表信息
     */
    public static List<String> listTables(JdbcTemplate template) {
        try {
            return template.query("SHOW TABLES;", new RowMapper<String>() {
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString(1);
                }
            }, null);
        } catch (Exception e) {
            log.error("获取数据表信息失败：{}", e.getMessage());
        }
        return null;
    }

    /**
     * 过滤MySQL默认数据库
     */
    public static List<String> filterDatabase(List<String> list) {
        if (CollectionUtil.isEmpty(list)) {
            return null;
        }
        List<String> resList = new ArrayList<>();
        for (String item : list) {
            if (!FILTER_DATABASE.contains(item)) {
                resList.add(item);
            }
        }
        return resList;
    }

    //获取备份数据库连接信息
    public static HikariDataSource getConnByBackupJobVO(BackupJobVO jobVO){
        NodeProperties nodeProperties = new NodeProperties();
        nodeProperties.setClassName(jobVO.getClassName());
        nodeProperties.setHost(jobVO.getHost());
        nodeProperties.setPort(jobVO.getPort());
        nodeProperties.setUsername(jobVO.getUsername());
        nodeProperties.setPassword(jobVO.getPassword());
        nodeProperties.setDatabase(jobVO.getDatabase());
        return DataSourceUtil.createDataSource(nodeProperties);
    }

    //创建表语句
//    CREATE TABLE `backup_set` (
//            `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
//  `memo` varchar(200) DEFAULT NULL COMMENT '说明',
//            `val` varchar(200) DEFAULT NULL COMMENT 'val,一旦设定不可更改',
//    PRIMARY KEY (`id`)
//) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    public static String joinCreateTableSql(String tableName, List<FieldVO> fieldVOList){
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(CREATE_TABLE).append(SPLIT).append(tableName).append(SPLIT).append(SPACE).append(LEFTBRACE).append(SPACE).append(CREATE_TABLE_ID).append(COMMA);

        for(FieldVO fieldVO : fieldVOList){
           stringBuilder.append(SPLIT)
                   .append(fieldVO.getKey())
                   .append(SPLIT)
                   .append(CREATE_TABLE_VARCHAR)
                   .append(QUOTES)
                   .append(fieldVO.getVal())
                   .append(QUOTES)
                   .append(COMMA);
        }
        stringBuilder.append(CREATE_TABLE_PRIMARY).append(SPACE).append(RIGHTBRACE).append(CREATE_TABLE_CHARSET).append(BRANCH);
        return stringBuilder.toString();
    }

    /**
     * 拼接insert数据
     */
    public static List<String> insertSql(String tableName, int headLine, List<FieldVO> fieldVOList, LinkedHashMap<Integer,Map<Integer, String>> list) {
        List<String> sqlList = new ArrayList<>();
        int size = list.size();
//        System.out.println("size：" + size);
        if (list.size() < 1) {
            //拼接insert语句
            return sqlList;
        }

        //每一条都是单独的insert语句
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append(ONESQL_PREFIX).append(INSERT_INTO).append(SPLIT).append(tableName).append(SPLIT).append(SPACE).append(LEFTBRACE);

        //拼接字段
        for (FieldVO fieldVO : fieldVOList) {
            stringBuilder.append(SPLIT).append(fieldVO.getKey()).append(SPLIT).append(COMMA);
        }

        stringBuilder.deleteCharAt(stringBuilder.length()-1);

        stringBuilder.append(RIGHTBRACE).append(VALUES).append(LEFTBRACE);



        //循环每一条数据
        int max = size + headLine;
        for (int i = headLine; i < max; i++) {
            Map<Integer, String> map = list.get(i);
//            System.out.println(JSON.toJSONString(map));
            if (null == map) {
                break;
            }
//            System.out.println("第"+i+"行");
            StringBuilder builder = new StringBuilder();
            builder.append(stringBuilder);

            //循环每一个map, 拼接values里的内容
            for (Integer key : map.keySet()) {
                builder.append(QUOTES).append(replaceKeyString(map.get(key))).append(QUOTES);
                //加一个个逗号
                builder.append(COMMA);
            }
            builder.deleteCharAt(builder.length()-1);
            builder.append(RIGHTBRACE).append(BRANCH);
            sqlList.add(builder.toString());
        }
        return sqlList;
    }
}
